需求：

       有两张表A,B。表A和表B有共同的字段，id，dev_ip，collector_id. 其中id是作为两个表关联的唯一标识。即表A中的id与表B中的id是一一对应的。本来B表中的字段collector_id应该是完全一致的，但是由于某些原因B表中的collect_id值被修改了，现在需要将A,B表的collector_id同步成一致。即将A表中与B表id和dev_ip一样的数据行更新B表中的collector_id为A表中的collector_id值。

实现方法有以下几种:

tb_device_info表为B表，tb_device表为A表

方法一：

    update tb_device_info di set collectorid = (select d.collector_id from tb_device d where d.id = di.deviceid )  where deviceid =  (select d.id from  tb_device d where d.id = di.deviceid and di.collectorid d.collector_id)

方法二：道理同方法一。

    update tb_device_info di set collectorid = (select d.collector_id from tb_device d where d.id = di.deviceid )  where （select 1  from  tb_device d where d.id = di.deviceid and di.collectorid d.collector_id)

方法三：原理是更新已查条件结果集，相当于更新同一个表中的两个不同字段。

    update (select di.deviceid ,di.dev_ip ,di.collectorid ,d.id ,d.dev_ip ,d.collector_id from tb_device_info di ,tb_device d where di.deviceid = d.id and di.collectorid d.collector_id) set collectorid= collector_id 

 方法四：存储过程的方式

declare  cursor cur_collecot is select collector_id,id from tb_device;     begin            for my_cur in cur_collecot loop             update tb_devic_info set collectorid  = my_cur.collector_id                 where deviceid = my_cur.id;        end loop; 

以上方法参考网络文章终结，记录以备使用

另转载网络牛人原文

为了做分析，需要整合一些数据到一个表中，涉及到通过主键关联多个表，获取其中的某些字段的值，通过update可以简单实现，找了一篇文章，挺不错：

    批量更新表时，update一个表的列时，需要依赖另外的表，这种依赖可以是where条件子句，也可以要update的field的值依赖另外的表   通常有两种做法   1.使用存储过程   2.在程序代码里逐条循环执行这里给出一种更高效、简洁的做法，批量更新SQL ，一句SQL就可以替代麻烦的循环过程，有MS SQLServer、Oracle、DB2下的写法--关键点：t4和t1是同一个table，primary key肯定也是同一个，--并以它进行关联，这样在 select语句里即可引用到要update的表的fields UPDATE Table1 AS t1 SET (Field1,Field2) = (SELECT Field21, Field22                        FROM Table2 t2                             INNER JOIN Table3 t3                                  ON t3.Field31 = t2.Field23                             INNER JOIN Table4 t4                                  ON t3.Field32 = t4.Filed41                        WHERE t2.Field24 >= ''                                       AND t1.fId = t4.fId); ----------------------------MS SQLServer --------------------------------------

UPDATE t1 SET Field1 = Field21, Field2 = Field22 FROM Table2 t2          INNER JOIN Table3 t3               ON t3.Field31 = t2.Field23          INNER JOIN Table4 t4               ON t3.Field32 = t4.Filed41 WHERE ((t2.Field24 >= '')                   AND t1.fId = t4.fId); ----------------------------Oracle --------------------------------------------

UPDATE Table1 t1 SET (Field1,Field2) = (SELECT Field21, Field22                        FROM Table2 t2                             INNER JOIN Table3 t3                                  ON t3.Field31 = t2.Field23                             INNER JOIN Table4 t4                                  ON t3.Field32 = t4.Filed41                        WHERE ((t2.Field24 >= '')                                  AND t1.fId = t4.fId)) WHERE EXISTS (SELECT Field21, Field22               FROM Table2 t2                    INNER JOIN Table3 t3                         ON t3.Field31 = t2.Field23                    INNER JOIN Table4 t4                         ON t3.Field32 = t4.Filed41               WHERE ((t2.Field24 >= '')                                AND t1.fId = t4.fId)); ---------------------------------DB2 ------------------------------------------

UPDATE Table1 AS t1 SET (Field1,Field2) = (SELECT Field21, Field22                        FROM Table2 t2                             INNER JOIN Table3 t3                                  ON t3.Field31 = t2.Field23                             INNER JOIN Table4 t4                                  ON t3.Field32 = t4.Filed41                        WHERE ((t2.Field24 >= '')                                          AND t1.fId = t4.fId)) WHERE EXISTS (SELECT Field21, Field22               FROM Table2 t2                    INNER JOIN Table3 t3                         ON t3.Field31 = t2.Field23                    INNER JOIN Table4 t4                         ON t3.Field32 = t4.Filed41               WHERE ((t2.Field24 >= '')                                 AND t1.fId = t4.fId));